package com.example.UI.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;
import com.example.UI.entity.User;

import java.util.ArrayList;
import java.util.List;

public class MySQLDataHelper extends SQLiteOpenHelper {
    private static final String DB_NAME="users.db";

    private static final String create_users =
            "CREATE TABLE users (" +
                    "student_id VARCHAR(11) PRIMARY KEY UNIQUE," + // 限制 student_id 为11个字符
                    "name VARCHAR(50)," +
                    "class_name VARCHAR(50)," +
                    "username VARCHAR(50)," +
                    "password VARCHAR(50)" +
                    ")";
    public MySQLDataHelper(@Nullable Context context) {
        super(context, DB_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(create_users);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 当数据库版本升级时，您可以在这里写入更新数据库的逻辑
        // 例如，如果增加了新的表或列，可以在这里添加创建语句
    }

    public long register(User u) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("student_id", u.getStudent_id());
        cv.put("name", u.getName());
        cv.put("class_name", u.getClass_name());
        cv.put("username", u.getUsername());
        cv.put("password", u.getPassword()); // 注意：这里应该使用散列密码

        long id = -1;
        String studentId = u.getStudent_id();

        // 检查学号是否已存在
        String[] columns = { "student_id" };
        String selection = "student_id = ?";
        String[] selectionArgs = { studentId };
        Cursor cursor = null;
        boolean studentIdExists = false;

        try {
            cursor = db.query("users", columns, selection, selectionArgs, null, null, null);
            studentIdExists = cursor.getCount() > 0; // 如果游标有数据，说明学号已存在
        } catch (Exception e) {
            Log.e("MySQLDataHelper", "Exception checking if student_id exists: ", e);
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }

        if (studentIdExists) {
            // 学号已存在，显示“已注册”
            Log.e("MySQLDataHelper", "student_id already registered!");
        } else {
            // 学号不存在，可以插入新记录
            db.beginTransaction();
            try {
                id = db.insert("users", null, cv);
                if (id == -1) {
                    Log.e("MySQLDataHelper", "Error inserting data, id returned: " + id);
                } else {
                    db.setTransactionSuccessful(); // 设置事务成功
                }
            } catch (Exception e) {
                Log.e("MySQLDataHelper", "Exception in register: ", e);
            } finally {
                db.endTransaction(); // 结束事务
            }
        }

        db.close(); // 关闭数据库
        return id;
    }

    public boolean login(String studentId, String inputPassword) {
        SQLiteDatabase db = this.getReadableDatabase();
        boolean result = false;
        String[] columns = { "student_id", "password" };
        String selection = "student_id = ?"; // 确保这里使用的是学号的字段
        String[] selectionArgs = { studentId };
        Cursor cursor = null;
        try {
            cursor = db.query("users", columns, selection, selectionArgs, null, null, null);
            if (cursor != null && cursor.moveToFirst()) {
                int passwordIndex = cursor.getColumnIndex("password");
                if (passwordIndex != -1) {
                    String storedPassword = cursor.getString(passwordIndex);
                    String hashedInputPassword = hashFunction(inputPassword);
                    result = storedPassword.equals(hashedInputPassword);
                } else {
                    Log.e("MySQLDataHelper", "Password column not found");
                }
            }
        } catch (Exception e) {
            Log.e("MySQLDataHelper", "Exception in login: ", e);
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return result;
    }

    // 示例散列函数，您需要根据实际情况替换为您的散列算法
    private String hashFunction(String input) {
        // 实现密码散列逻辑
        return input; // 仅为示例，不要在生产中使用
    }


    // MySQLDataHelper.java
    public boolean updatePassword(String studentId, String newPassword) {
        SQLiteDatabase db = this.getWritableDatabase();
        boolean isUpdated = false;
        ContentValues cv = new ContentValues();
        cv.put("password", newPassword); // 注意：这里应该使用散列密码

        String selection = "student_id = ?";
        String[] selectionArgs = { studentId };

        int rowsUpdated = db.update("users", cv, selection, selectionArgs);

        if (rowsUpdated > 0) {
            isUpdated = true;
        }

        db.close();
        return isUpdated;
    }


    // MySQLDataHelper.java
    public List<User> getAllUsers() {
        SQLiteDatabase db = this.getReadableDatabase();
        List<User> users = new ArrayList<>();
        String[] columns = { "class_name", "student_id", "name" }; // 指定需要查询的列
        Cursor cursor = null;
        try {
            cursor = db.query("users", columns, null, null, null, null, null);
            if (cursor != null) {
                while (cursor.moveToNext()) {
                    String className = cursor.getString(cursor.getColumnIndex("class_name"));
                    String studentId = cursor.getString(cursor.getColumnIndex("student_id"));
                    String name = cursor.getString(cursor.getColumnIndex("name"));
                    User user = new User(className, studentId, name); // 使用指定的构造方法创建User对象
                    users.add(user);
                }
            }
        } catch (Exception e) {
            Log.e("MySQLDataHelper", "Error retrieving all users", e);
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }
        return users;
    }
}